﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;

namespace PozrSvc
{
    public interface DatabaseClient : IDisposable
    {
        string DataPath { get; set; }

        bool UpdateBorder (string hash, double b_lat, double b_lon, short b_signal);
        bool UpdateCenter(string hash, double c_lat, double c_lon, short c_signal);

        DataTable GetPairs();
    }

    public class DummyClient : DatabaseClient
    {
        public string DataPath { get; set; }

        public DummyClient()
        {
            DataPath = Middleware.ApplicationDirectory;
        }

        public bool UpdateBorder(string hash, double b_lat, double b_lon, short b_signal)
        {
            return false;
        }

        public bool UpdateCenter(string hash, double c_lat, double c_lon, short c_signal)
        {
            return false;
        }

        public DataTable GetPairs()
        {
            return new DataTable();
        }

        public void Dispose()
        {
            // Dummy
        }
    }

    public class SqlCeClient : DatabaseClient
    {
        SqlCeConnection Connection = null;

        public string DataPath { get; set; }

        public SqlCeClient()
        {
            DataPath = Middleware.ApplicationDirectory + "\\pdb.sdf";

            // Check if SqlCe is installed
            SqlCeEngine sqlEngine = null;
            try
            {
                sqlEngine = new SqlCeEngine();
                Middleware.OnLogEvent("SqlCe engine created.");
            }
            catch (Exception ex)
            {
                Middleware.OnLogEvent(ex.Message);
                throw new MissingMethodException("SQL CE is not installed! Local database disabled.");
            }

            // Check the database file, if exist open it, if not, create it
            Connection = new SqlCeConnection("Data Source=" + DataPath);
            try
            {
                Connection.Open();
            }
            catch (Exception)
            {
                CreateDatabase();
            }
        }

        public void CreateDatabase()
        {
            try
            {
                // Creating the database
                SqlCeEngine SqlEngine = new SqlCeEngine("data source=" + DataPath + ";Max Buffer Size = 512");
                SqlEngine.CreateDatabase();
                Connection = new SqlCeConnection("Data Source=" + DataPath);
                Connection.Open();    

                // Creating tables
                SqlCeCommand Command = Connection.CreateCommand();
                Command.CommandText = "CREATE TABLE PairData (SourceId nvarchar(50) PRIMARY KEY, BLat float, BLon float, CLat float, CLon float, BSig smallint, CSig smallint, TimeMod datetime, Relevance float, Name nvarchar(100), Flags nvarchar(10))";
                Command.ExecuteNonQuery();

                Middleware.OnLogEvent("Database file created.");
            }
            catch (Exception ex)
            {
                Middleware.OnLogEvent(ex.Message);
                
                throw new NullReferenceException("Database file cannot be created. Local database disabled.");
            }
        }

        public void Dispose()
        {
            try
            {
                if (Connection != null)
                {
                    Connection.Close();

                }
            }
            catch (Exception ex)
            {
                Middleware.WriteLogToFile(ex.Message);
            }
        }
        
        public bool UpdateBorder(string hash, double b_lat, double b_lon, short b_signal)
        {
            SqlCeCommand cmd = Connection.CreateCommand();

            cmd.CommandText = "SELECT * FROM PairData WHERE SourceId = @hash";
            cmd.Parameters.Add("@hash", hash);
            SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);

            if (rs.HasRows)
            {
                rs.ReadFirst();

                if (rs.IsDBNull(5) || rs.GetInt16(5) > b_signal)
                {
                    rs.SetDouble(1, b_lat);
                    rs.SetDouble(2, b_lon);
                    rs.SetInt16(5, b_signal);
                    rs.SetDateTime(7, DateTime.Now);
                    rs.Update();

                    cmd.Dispose();
                    return true;
                }
            }
            else
            {
                SqlCeUpdatableRecord rec = rs.CreateRecord();
                rec.SetString(0, hash);
                rec.SetDouble(1, b_lat);
                rec.SetDouble(2, b_lon);
                rec.SetInt16(5, b_signal);
                rec.SetDateTime(7, DateTime.Now);

                rs.Insert(rec);
                return true;
            }
            return false;
        }

        public bool UpdateCenter(string hash, double c_lat, double c_lon, short c_signal)
        {
            SqlCeCommand cmd = Connection.CreateCommand();

            cmd.CommandText = "SELECT * FROM PairData WHERE SourceId = @hash";
            cmd.Parameters.Add("@hash", hash);
            SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);

            if (rs.HasRows)
            {
                rs.ReadFirst();

                if (rs.IsDBNull(6) || rs.GetInt16(6) < c_signal)
                {
                    rs.SetDouble(3, c_lat);
                    rs.SetDouble(4, c_lon);
                    rs.SetInt16(6, c_signal);
                    rs.SetDateTime(7, DateTime.Now);
                    rs.Update();

                    cmd.Dispose();
                    return true;
                }
            }
            else
            {
                SqlCeUpdatableRecord rec = rs.CreateRecord();
                rec.SetString(0, hash);
                rec.SetDouble(3, c_lat);
                rec.SetDouble(4, c_lon);
                rec.SetInt16(6, c_signal);
                rec.SetDateTime(7, DateTime.Now);

                rs.Insert(rec);
                return true;
            }
            return false;
        }

        public DataTable GetPairs()
        {
            SqlCeCommand Command = Connection.CreateCommand();
            Command.CommandText = "SELECT SourceId, CLat, CLon, BLon, BLat, TimeMod FROM PairData";
            SqlCeDataReader Reader = Command.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("SourceId");
            dt.Columns.Add("CLat");
            dt.Columns.Add("CLon");
            dt.Columns.Add("BLat");
            dt.Columns.Add("BLon");
            dt.Columns.Add("TimeMod");

            if (Reader != null)
            {
                object[] row = new object[4];
                while (Reader.Read())
                {
                    Reader.GetValues(row);
                    dt.Rows.Add(row);
                    System.Windows.Forms.Application.DoEvents();
                }
            }

            return dt;
        }
    }
}
